import sys

import openpyxl
from PyQt5.QtWidgets import QMainWindow, QApplication, QGraphicsObject
from untitled import Ui_MainWindow
import threading
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
import datetime
from PyQt5.QtWidgets import QFileDialog, QGraphicsObject


class tebles(QMainWindow, Ui_MainWindow, QGraphicsObject):

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.filepath = None
        self.fileName = None
        self.pushButton2.clicked.connect(self.open_file)

        self.pushButton.clicked.connect(lambda: self.thread_j(self.biaoge))
        self.pushButton1.clicked.connect(lambda: self.thread_j(self.Ns_school))
        print(self.filepath is None)
        print(self.fileName is None)



    # def fangwei(self):
    #
    #     with open('config.inf', 'r') as f:
    #         file1 = f.read()
    #         file = file1.split('=')
    #         file2 = ''.join(file[1])
    #         key1 = file2[-4:-2]
    #         key = int(key1) - 1
    #         if key < 10:
    #             key2 = '0' + str(key)
    #             file4 = file2.replace(file2[-4:-2], key2)
    #         else:
    #             file4 = file2.replace(file2[-4:-2], str(key))
    #
    #     with open('config.inf', 'w') as f:
    #         f.write(f'MD5 ' + '=' + file4)
    #         f.close()
    #
    #     if starttime < datetime.datetime(2023, 11, 1):
    #         self.pushButton.clicked.connect(lambda: self.thread_j(self.biaoge))
    #         self.pushButton1.clicked.connect(lambda: self.thread_j(self.Ns_school))
    #         self.Qtext_A('本程序运行期间不要打开任何表格文件！切记切记！班级名称不能超过10个汉字尽量简短！')
    #     else:
    #         self.pushButton.setEnabled(False)
    #         self.pushButton1.setEnabled(False)
    #     if key <= 0:
    #         self.pushButton.setEnabled(False)
    #         self.pushButton1.setEnabled(False)
    #     else:
    #         self.pushButton.clicked.connect(lambda: self.thread_j(self.biaoge))
    #         self.pushButton1.clicked.connect(lambda: self.thread_j(self.Ns_school))

    def Qtext_A(self, message):
        self.textBrowser.append(message)

    def thread_j(self, massage):
        t = threading.Thread(target=massage, daemon=True)
        t.start()

    def biaoge(self):
        workbook = openpyxl.load_workbook(filename=self.filepath)
        workbook1 = Workbook()
        sheet1 = workbook['Sheet1']['A']
        for i in sheet1:
            sheet = workbook1.create_sheet(f'{i.value}')
            self.Qtext_A(f'创建{i.value}完成')
            sheet.merge_cells("I1:K1")
            sheet.merge_cells("L1:X1")
            sheet.merge_cells("Y1:Z1")
            sheet.merge_cells("AA1:AD1")
            sheet.merge_cells("AE1:AH1")
            sheet.merge_cells("AI1:AL1")
            sheet.merge_cells("AM1:AN1")
            sheet.merge_cells("AO1:AS1")
            sheet['A1'].alignment = Alignment(horizontal="center")
            sheet['I1'] = '内科'
            sheet['I1'].alignment = Alignment(horizontal="center")
            sheet['L1'] = '外科体检结果'
            sheet['L1'].alignment = Alignment(horizontal="center")
            sheet['Y1'] = '矫正视力'
            sheet['Y1'].alignment = Alignment(horizontal="center")
            sheet['AA1'] = '裸视力'
            sheet['AA1'].alignment = Alignment(horizontal="center")
            sheet['AE1'] = '左眼'
            sheet['AE1'].alignment = Alignment(horizontal="center")
            sheet['AI1'] = '右眼'
            sheet['AI1'].alignment = Alignment(horizontal="center")
            sheet['AM1'] = '眼科'
            sheet['AM1'].alignment = Alignment(horizontal="center")
            sheet['AO1'] = '五官科'
            sheet['AO1'].alignment = Alignment(horizontal="center")
            sheet['A2'] = '录入者'
            sheet['A2'].alignment = Alignment(horizontal="center")
            sheet['B2'] = '班级'
            sheet['B2'].alignment = Alignment(horizontal="center")
            sheet['C2'] = '序号'
            sheet['C2'].alignment = Alignment(horizontal="center")
            sheet['D2'] = '姓名'
            sheet['D2'].alignment = Alignment(horizontal="center")
            sheet['E2'] = '性别'
            sheet['E2'].alignment = Alignment(horizontal="center")
            sheet['F2'] = '身份证号'
            sheet['F2'].alignment = Alignment(horizontal="center")
            sheet['G2'] = '到检情况'
            sheet['G2'].alignment = Alignment(horizontal="center")
            sheet['H2'] = '年龄'
            sheet['H2'].alignment = Alignment(horizontal="center")
            sheet['I2'] = '心'
            sheet['I2'].alignment = Alignment(horizontal="center")
            sheet['J2'] = '肺'
            sheet['J2'].alignment = Alignment(horizontal="center")
            sheet['K2'] = '肝/脾'
            sheet['K2'].alignment = Alignment(horizontal="center")
            sheet['L2'] = '身高(米)'
            sheet['L2'].alignment = Alignment(horizontal="center")
            sheet['M2'] = '体重(KG)'
            sheet['M2'].alignment = Alignment(horizontal="center")
            sheet['N2'] = 'BMI'
            sheet['N2'].alignment = Alignment(horizontal="center")
            sheet['O2'] = 'BMI修正'
            sheet['O2'].alignment = Alignment(horizontal="center")
            sheet['P2'] = 'BMI比对结果'
            sheet['P2'].alignment = Alignment(horizontal="center")
            sheet['Q2'] = '身高比对结果'
            sheet['Q2'].alignment = Alignment(horizontal="center")
            sheet['R2'] = '头部'
            sheet['R2'].alignment = Alignment(horizontal="center")
            sheet['S2'] = '颈部'
            sheet['S2'].alignment = Alignment(horizontal="center")
            sheet['T2'] = '胸部'
            sheet['T2'].alignment = Alignment(horizontal="center")
            sheet['U2'] = '脊柱'
            sheet['U2'].alignment = Alignment(horizontal="center")
            sheet['V2'] = '四肢'
            sheet['V2'].alignment = Alignment(horizontal="center")
            sheet['W2'] = '皮肤'
            sheet['W2'].alignment = Alignment(horizontal="center")
            sheet['X2'] = '淋巴结'
            sheet['X2'].alignment = Alignment(horizontal="center")
            sheet['Y2'] = '左'
            sheet['Y2'].alignment = Alignment(horizontal="center")
            sheet['Z2'] = '右'
            sheet['Z2'].alignment = Alignment(horizontal="center")
            sheet['AA2'] = '左'
            sheet['AA2'].alignment = Alignment(horizontal="center")
            sheet['AB2'] = '右'
            sheet['AB2'].alignment = Alignment(horizontal="center")
            sheet['AC2'] = '屈光'
            sheet['AC2'].alignment = Alignment(horizontal="center")
            sheet['AD2'] = '视力评价'
            sheet['AD2'].alignment = Alignment(horizontal="center")
            sheet['AE2'] = '球镜'
            sheet['AE2'].alignment = Alignment(horizontal="center")
            sheet['AF2'] = '柱镜'
            sheet['AF2'].alignment = Alignment(horizontal="center")
            sheet['AG2'] = '轴位'
            sheet['AG2'].alignment = Alignment(horizontal="center")
            sheet['AH2'] = '佩戴OK镜'
            sheet['AH2'].alignment = Alignment(horizontal="center")
            sheet['AI2'] = '球镜'
            sheet['AI2'].alignment = Alignment(horizontal="center")
            sheet['AJ2'] = '柱镜'
            sheet['AJ2'].alignment = Alignment(horizontal="center")
            sheet['AK2'] = '轴位'
            sheet['AK2'].alignment = Alignment(horizontal="center")
            sheet['AL2'] = '佩戴OK镜'
            sheet['AL2'].alignment = Alignment(horizontal="center")
            sheet['AM2'] = '结膜炎'
            sheet['AM2'].alignment = Alignment(horizontal="center")
            sheet['AN2'] = '沙眼'
            sheet['AN2'].alignment = Alignment(horizontal="center")
            sheet['AO2'] = '耳'
            sheet['AO2'].alignment = Alignment(horizontal="center")
            sheet['AP2'] = '鼻'
            sheet['AP2'].alignment = Alignment(horizontal="center")
            sheet['AQ2'] = '喉/扁桃体'
            sheet['AQ2'].alignment = Alignment(horizontal="center")
            sheet['AR2'] = '龋齿'
            sheet['AR2'].alignment = Alignment(horizontal="center")
            sheet['AS2'] = '牙周'
            sheet['AS2'].alignment = Alignment(horizontal="center")
            sheet['AT2'] = '肺活量'
            sheet['AT2'].alignment = Alignment(horizontal="center")
        sheet2 = workbook1['Sheet']
        workbook1.remove(sheet2)
        workbook1.save(filename=f'文件/{self.fileName}.xlsx')
        return self.Qtext_A(f'{self.fileName}文件创建完成')

    def Ns_school(self):
        #                   水平对齐              垂直对齐            自动换行
        align = Alignment(horizontal="center", vertical="center", wrap_text=True)
        font = Font(name="宋体", size=9)
        workbook = openpyxl.load_workbook(filename=self.filepath)
        sheet1 = workbook['Sheet1']['A']
        wb = Workbook()
        for i in sheet1:
            sheet = wb.create_sheet(f'{i.value}')
            self.Qtext_A(f'开始创建{i.value}')
            sheet['B1'] = '学年'
            sheet['B1'].alignment = align

            sheet['C1'] = '第一学年'
            sheet['C1'].alignment = align

            sheet['A2'] = '序号'
            sheet['A2'].alignment = align
            sheet.merge_cells('A2:A4')

            sheet['B2'] = '年级班级'
            sheet.merge_cells('B2:B4')
            sheet['B2'].alignment = align

            sheet['C2'] = '学生姓名'
            sheet.merge_cells('C2:C4')
            sheet['C2'].alignment = align

            sheet['D2'] = '身份证号码'
            sheet.merge_cells('D2:D4')
            sheet['D2'].alignment = align

            sheet['E2'] = '性别'
            sheet.merge_cells('E2:E4')
            sheet['E2'].alignment = align

            sheet['F2'] = '年龄'
            sheet.merge_cells('F2:F4')
            sheet['F2'].alignment = align

            sheet['G2'] = '到检情况'
            sheet.merge_cells('G2:G4')
            sheet['G2'].alignment = align

            sheet['H2'] = '学籍号'
            sheet.merge_cells('H2:H4')
            sheet['H2'].alignment = align

            sheet['I2'] = '查期'
            sheet.merge_cells('I2:I4')
            sheet['I2'].alignment = align

            sheet['J2'] = '发育及营养状况'
            sheet.merge_cells('J2:J4')
            sheet['J2'].alignment = align

            sheet['K2'] = '身高(cm)'
            sheet.merge_cells('K2:K4')
            sheet['K2'].alignment = align

            sheet['L2'] = '体重(kg)'
            sheet.merge_cells('L2:L4')
            sheet['L2'].alignment = align

            sheet['M2'] = '胸围(cm)'
            sheet.merge_cells('M2:M4')
            sheet['M2'].alignment = align

            sheet['N2'] = '肺活量(ml)'
            sheet.merge_cells('N2:N4')
            sheet['N2'].alignment = align

            sheet['O2'] = '血压(mmhg)'
            sheet.merge_cells('O2:O4')
            sheet['O2'].alignment = align

            sheet['P2'] = '脉搏(次/分)'
            sheet.merge_cells('P2:P4')
            sheet['P2'].alignment = align

            sheet['Q2'] = '视力'
            sheet.merge_cells('Q2:V2')
            sheet['Q2'].alignment = align

            sheet['Q3'] = '左'
            sheet.merge_cells('Q3:S3')
            sheet['Q3'].alignment = align

            sheet['T3'] = '右'
            sheet.merge_cells('T3:V3')
            sheet['T3'].alignment = align

            sheet['Q4'] = '裸眼视力'
            sheet['Q4'].alignment = align

            sheet['R4'] = '矫正视力'
            sheet['R4'].alignment = align

            sheet['S4'] = '矫正度数'
            sheet['S4'].alignment = align

            sheet['T4'] = '裸眼视力'
            sheet['T4'].alignment = align

            sheet['U4'] = '矫正视力'
            sheet['U4'].alignment = align

            sheet['V4'] = '矫正度数'
            sheet['V4'].alignment = align

            sheet['W2'] = '听力'
            sheet.merge_cells('W2:X2')
            sheet['W2'].alignment = align

            sheet['W3'] = '左'
            sheet['W3'].alignment = align

            sheet['X3'] = '右'
            sheet['X3'].alignment = align

            sheet['Y2'] = '沙眼'
            sheet.merge_cells('Y2:Y4')
            sheet['Y2'].alignment = align

            sheet['Z2'] = '色觉'
            sheet.merge_cells('Z2:Z4')
            sheet['Z2'].alignment = align

            sheet['AA2'] = '鼻病'
            sheet.merge_cells('AA2:AA4')
            sheet['AA2'].alignment = align

            sheet['AB2'] = '口腔'
            sheet.merge_cells('AB2:AB4')
            sheet['AB2'].alignment = align

            sheet['AC2'] = '心脏'
            sheet.merge_cells('AC2:AC4')
            sheet['AC2'].alignment = align

            sheet['AD2'] = '肺'
            sheet.merge_cells('AD2:AD4')
            sheet['AD2'].alignment = align

            sheet['AE2'] = '肝脾'
            sheet.merge_cells('AE2:AE4')
            sheet['AE2'].alignment = align

            sheet['AF2'] = '胃肠'
            sheet.merge_cells('AF2:AF4')
            sheet['AF2'].alignment = align

            sheet['AG2'] = '脊柱'
            sheet.merge_cells('AG2:AG4')
            sheet['AG2'].alignment = align

            sheet['AH2'] = '四肢'
            sheet.merge_cells('AH2:AH4')
            sheet['AH2'].alignment = align

            sheet['AI2'] = '平足'
            sheet.merge_cells('AI2:AI4')
            sheet['AI2'].alignment = align

            sheet['AJ2'] = '皮肤'
            sheet.merge_cells('AJ2:AJ4')
            sheet['AJ2'].alignment = align

            sheet['AK2'] = '传染病'
            sheet.merge_cells('AK2:AK4')
            sheet['AK2'].alignment = align

            sheet['AL2'] = '胸透'
            sheet.merge_cells('AL2:AL4')
            sheet['AL2'].alignment = align

            sheet['AM2'] = '既往病史'
            sheet.merge_cells('AM2:AM4')
            sheet['AM2'].alignment = align

            sheet['AN2'] = '医院名或校医名'
            sheet.merge_cells('AN2:AN4')
            sheet['AN2'].alignment = align

            sheet['AO2'] = '头部'
            sheet.merge_cells('AO2:AO4')
            sheet['AO2'].alignment = align

            sheet['AP2'] = '颈部'
            sheet.merge_cells('AP2:AP4')
            sheet['AP2'].alignment = align

            sheet['AQ2'] = '胸部'
            sheet.merge_cells('AQ2:AQ4')
            sheet['AQ2'].alignment = align

            sheet['AR2'] = '淋巴'
            sheet.merge_cells('AR2:AR4')
            sheet['AR2'].alignment = align

            sheet['AS2'] = '扁桃体'
            sheet.merge_cells('AS2:AS4')
            sheet['AS2'].alignment = align

            sheet['AT2'] = 'BMI'
            sheet.merge_cells('AT2:AT4')
            sheet['AT2'].alignment = align

            sheet['AU2'] = 'BMI对比结果'
            sheet.merge_cells('AU2:AU4')
            sheet['AU2'].alignment = align

            sheet['AV2'] = '身高对比结果'
            sheet.merge_cells('AV2:AV4')
            sheet['AV2'].alignment = align

            sheet['AW2'] = '视力对比结果'
            sheet.merge_cells('AW2:AW4')
            sheet['AW2'].alignment = align

            sheet['AX2'] = '耳异常'
            sheet.merge_cells('AX2:AX4')
            sheet['AX2'].alignment = align

            sheet['AY2'] = '龋齿'
            sheet.merge_cells('AY2:AY4')
            sheet['AY2'].alignment = align

            sheet.row_dimensions[2].height = 19.5
            sheet.column_dimensions['A'].width = 8.38
            sheet.column_dimensions['B'].width = 11.88
            sheet.column_dimensions['C'].width = 14
            sheet.column_dimensions['D'].width = 20.13
            sheet.column_dimensions['E'].width = 15.63
            sheet.column_dimensions['F'].width = 7
            sheet.column_dimensions['G'].width = 7.5
            sheet.column_dimensions['H'].width = 7.5
            sheet.column_dimensions['I'].width = 7.5
            sheet.column_dimensions['J'].width = 7.5
            sheet.column_dimensions['K'].width = 7.5
            sheet.column_dimensions['L'].width = 11
            sheet.column_dimensions['M'].width = 8.38
            sheet.column_dimensions['N'].width = 8.38
            sheet.column_dimensions['O'].width = 8.38
            sheet.column_dimensions['P'].width = 8.38
            sheet.column_dimensions['Q'].width = 8.38
            sheet.column_dimensions['R'].width = 8.38
            sheet.column_dimensions['S'].width = 8.38
            sheet.column_dimensions['T'].width = 8.38
            sheet.column_dimensions['U'].width = 8.38
            sheet.column_dimensions['V'].width = 8.38
            sheet.column_dimensions['W'].width = 8.38
            sheet.column_dimensions['X'].width = 8.38
            sheet.column_dimensions['Y'].width = 15.75
            sheet.column_dimensions['Z'].width = 8.38
            sheet.column_dimensions['AA'].width = 8.38
            sheet.column_dimensions['AB'].width = 8.38
            sheet.column_dimensions['AC'].width = 8.38
            sheet.column_dimensions['AD'].width = 8.38
            sheet.column_dimensions['AE'].width = 8.38
            sheet.column_dimensions['AF'].width = 8.38
            sheet.column_dimensions['AG'].width = 8.38
            sheet.column_dimensions['AH'].width = 8.38
            sheet.column_dimensions['AI'].width = 11.88
            sheet.column_dimensions['AJ'].width = 11.88
            sheet.column_dimensions['AK'].width = 11.88
            sheet.column_dimensions['AL'].width = 11.88
            sheet.column_dimensions['AM'].width = 11.88
            sheet.column_dimensions['AN'].width = 11.88
            sheet.column_dimensions['AO'].width = 11.88
            sheet.column_dimensions['AP'].width = 11.88
            sheet.column_dimensions['AQ'].width = 11.88
            sheet.column_dimensions['AR'].width = 11.88
            sheet.column_dimensions['AS'].width = 11.88
            sheet.column_dimensions['AT'].width = 11.88
            sheet.column_dimensions['AU'].width = 11.88
            sheet.column_dimensions['AV'].width = 11.88
            sheet.column_dimensions['AW'].width = 11.88
            sheet.column_dimensions['AX'].width = 11.88
            sheet.column_dimensions['AY'].width = 11.88
            for row in sheet:
                for cell in row:
                    sheet[cell.coordinate].font = font
        sheet1 = wb['Sheet']
        wb.remove(sheet1)
        wb.save(filename=f'文件/{self.fileName}.xlsx')
        self.Qtext_A(f'创建{self.fileName}完成')

    def open_file(self):
        """
        按钮打开文件获取文件名及文件路径
        """
        filename, filetype = QFileDialog.getOpenFileName(self, "选取Excel文件", "./data", "Excel Files (*.xls *.xlsx)")
        self.Qtext_A(f'打开文件路径{filename}')
        # 将全局变量用来存储 文件路径
        self.filepath = filename
        # 将全局变量用来存储 处理以后的文件名
        self.fileName = self.filepath.split('/')[-1].split('.')[0]
        print(self.filepath is None)
        print(self.fileName is None)

    def input_file(self):
        pass



if __name__ == '__main__':
    starttime = datetime.datetime.today()
    app = QApplication(sys.argv)
    ui = tebles()
    ui.show()
    sys.exit(app.exec_())
